
/****** Object:  View [dbo].[VehicleDispatchFormInfo]    Script Date: 03/28/2014 16:48:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[VehicleDispatchFormInfo]
AS
SELECT     v.ID, v.ProjectName, v.Code, v.DispatchTime, v.PrincipalID, u.Name AS PrincipalName, v.Driver, v.DriverContactNo, v.VehicleOwnerContactNo, v.Status, 
                      d.DisplayName AS StatusName, v.IsActive, v.CreateDate, v.UpdateDate
                      ,v2.LicensePlateNo
                      ,vr.Code AS VehicleCode
FROM         dbo.VehicleDispatchForm AS v LEFT OUTER JOIN
                      dbo.DictionaryData AS d ON v.Status = d.ID LEFT OUTER JOIN
                      dbo.[User] AS u ON v.PrincipalID = u.ID
                      LEFT JOIN Vehicle v2 ON v2.ID = v.VehicleID
                      LEFT JOIN VehicleOwner vo ON vo.ID = v2.OwnerID
                      LEFT JOIN VehicleRegister vr ON vr.VehicleID = v2.ID AND YEAR(v.CreateDate)=vr.WorkYear AND MONTH(v.CreateDate)=vr.WorkMonth
                      

GO